
<%--
Document   : lists2choose
Created on : Apr 5, 2010, 11:42:09 PM
Author     : SAM
--%>

<%
String suid=(String)session.getAttribute("suid");
                if(suid==null||suid.equals(""))
                {
                        response.sendRedirect("../index.jsp");
                }
%>
<%@ page import="java.sql.*"%>
<%@ page language="java" import="java.util.*"%>
<%

            Connection connection = null;
            try {

                String driverName = "oracle.jdbc.driver.OracleDriver";
                Class.forName(driverName);

                // Create a connection to the database
                String serverName = "10.0.0.27";
                String portNumber = "1521";
                String sid = "XE";
                String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
                String uname = "aranya27";
                String pword = "doodledo";

                connection = DriverManager.getConnection(url, uname, pword);

%>

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <link href="PPP-files/styles_tujc1.css" type="text/css" rel="stylesheet"/>
        <style type="text/css">A#vbUL_tujc1a{display:none}</style>
        <script type="text/javascript">
            function aa (url) {
                window.location = url;
            }

            window.onload = function() {
                var t;
                for(var j=1;j<=19;j=j+1){
                    t="t"+j;
                    document.getElementById(t).style.display='none';
                }
            }

            function tab (tt) {
                for(var j=1;j<=19;j=j+1){
                    var t;
                     t="t"+j;
                    if (j==tt) {
                        document.getElementById(t).style.display='block';

                    }
                    else {
                        document.getElementById(t).style.display='none';

                    }
                }
            }


        </script>

        <title>Reports Page</title>
        <style type="text/css">A#vbUL_usdiqa{display:none}</style>
    </head>
    <body bgcolor="#6A6A6A">
        <% Statement st = null;
                        String str;
                        ResultSet rs;
        %>
        <script type="text/javascript"> var vbImgPath=""</script>
<script type="text/javascript" src="scusdiq.js"></script>
        <center>
            <table bgcolor="#E8E8E8" cellpadding="0" cellspacing="0"  width="780">

                <tr  align="center">
                    <td width="780" height="159" align="center" colspan="2">
                        <img border="0" src="../images/back.jpg" width="780" height="159" >
                    </td>
                </tr>
                <tr><td colspan="2">&nbsp;</td></tr>
                <tr><td colspan="2"><table id="vista-buttons.com:idusdiq" width="0" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-right:0px" title ="HOME                                                          ">
<a href="tasks.jsp"onmouseover='xpe("usdiqo");' onmouseout='xpe("usdiqn");'><img id="xpi_usdiq" src="btusdiq_0.png" name="vbusdiq" width="195" height="28" border="0" alt="HOME                                                          " /></a></td><td style="padding-right:0px" title ="REPORTS                                                        ">
<a href="totalstudDetails.jsp" onmouseover='xpe("vsdiqo");' onmouseout='xpe("vsdiqn");'><img id="xpi_vsdiq" src="btvsdiq_0.png" name="vbvsdiq" width="195" height="28" border="0" alt="REPORTS                                                        " /></a></td><td style="padding-right:0px" title ="CHANGE PASSWORD                                     ">
<a href="../changePassword.jsp" onmouseover='xpe("ysdiqo");' onmouseout='xpe("ysdiqn");'><img id="xpi_ysdiq" src="btysdiq_0.png" name="vbysdiq" width="195" height="28" border="0" alt="CHANGE PASSWORD                                     " /></a></td><td style="padding-right:0px" title ="LOG OUT                                                      ">
<a href="../logout.jsp" onmouseover='xpe("xsdiqo");' onmouseout='xpe("xsdiqn");'><img id="xpi_xsdiq" src="btxsdiq_0.png" name="vbxsdiq" width="195" height="28" border="0" alt="LOG OUT                                                      " /></a></td></tr></table>
                    </td></tr>

                <tr>
                    <td width="185" valign="top" bgcolor="#881c1c">
                        <table bgcolor="#ff000" width="185" border="0" cellspacing="2">
                            <tr>
                                <td valign="top" colspan="0" bgcolor="#D3D3D3" WIDTH="185"HEIGHT="100" >
                                    <ul id="vbUL_tujc1" class="vbULtujc1" style="visibility:hidden;">
                                        <li><a><span title="BIOTECHNOLOGY" class="text1" onCLick="tab(1)">BIOTECHNOLOGY</span></a></li>
                                        <li><a><span title="CHE" class="text1" onCLick="tab(2)">CHEMICAL&nbsp;ENGINEERING</span></a></li>
                                        <li><a><span title="CE" class="text1" onCLick="tab(3)">CIVIL&nbsp;ENGINEERING</span></a></li>
                                        <li><a><span title="CSE" class="text1" onCLick="tab(4)">COMPUTER&nbsp;SCIENCE&nbsp;ENGINEERING</span></a></li>
                                        <li><a><span title="ECE" class="text1" onCLick="tab(5)">ELECTRONICS&nbsp;AND&nbsp;COMMUNICATION&nbsp;ENGINEERING</span></a></li>
                                        <li><a><span title="EE" class="text1" onCLick="tab(6)">ELECTRICAL&nbsp;ENGINEERING</span</a></li>
                                        <li><a><span title="IT" class="text1" onCLick="tab(7)">INFORMATION&nbsp;TECHNOLOGY</span></a></li>
                                        <li><a><span title="ME" class="text1" onCLick="tab(8)">MECHANICAL&nbsp;ENGINEERING</span></a></li>
                                        <li><a><span title="MME" class="text1" onCLick="tab(9)">METALLURGICAL&nbsp;AND&nbsp;MATERIAL&nbsp;SCIENCES</span></a></li></ul>

                                    <ul id="vbUL_oujc1" class="vbULtujc1" style="visibility:hidden;">
                                        <li><a><span title="GEN" class="text1" onCLick="tab(10)">GEN</span></a></li>
                                        <li><a><span title="OBC" class="text1" onCLick="tab(11)">OBC</span></a></li>
                                        <li><a><span title="SC" class="text1" onCLick="tab(12)">SC</span></a></li>
                                        <li><a><span title="ST" class="text1" onCLick="tab(13)">ST</span></a></li></ul>

                                    <ul id="vbUL_4ujc1" class="vbULtujc1" style="visibility:hidden;">
                                        <li><a><span title="NIT BOYS HOSTEL" class="text1" onClick="tab(14)">NIT BOYS HOSTEL</span></a></li>
                                        <li><a><span title="NIT GIRLS HOSTEL" class="text1" onClick="tab(15)">NIT GIRLS HOSTEL</span></a></li></ul>
                                    <script type="text/javascript"> var vbImgPath="PPP-files/"</script>
                                    <script type="text/javascript" src="PPP-files/sctujc1.js"></script>
                                    <table id="vista-buttons.com:idtujc1" width="20" cellpadding="0" cellspacing="0" border="0"><tr><td style="padding-bottom:0px" title ="BRANCH WISE STUDENTS                                      ">
                                                <a onmouseover='xpe("tujc1o");xpshow("tujc1",1,this);xpsmover(this);' onmouseout='xpsmout(this);'><img id="xpi_tujc1" src="PPP-files/bttujc1_0.png" name="vbtujc1" width="185" height="25" border="0" alt="BRANCH WISE STUDENTS                                      " /></a></td></tr><tr><td style="padding-bottom:0px" title ="STUDENTS ENROLLED                                                 ">
                                                <a onmouseover='xpe("pujc1o");' onmouseout='xpe("pujc1n");' onclick="tab(16)"><img id="xpi_pujc1" src="PPP-files/btpujc1_0.png" name="vbpujc1" width="185" height="25" border="0" alt="STUDENTS ENROLLED                                                 " /></a></td></tr><tr><td style="padding-bottom:0px" title =" CATEGORY WISE STUDENTS                                  ">
                                                <a onmouseover='xpe("oujc1o");xpshow("oujc1",1,this);xpsmover(this);' onmouseout='xpsmout(this);'><img id="xpi_oujc1" src="PPP-files/btoujc1_0.png" name="vboujc1" width="185" height="25" border="0" alt=" CATEGORY WISE STUDENTS                                  " /></a></td></tr><tr><td style="padding-bottom:0px" title ="STUDENT AND RANKS                                                ">
                                                <a onmouseover='xpe("wujc1o");' onmouseout='xpe("wujc1n");' onclick="tab(17)"><img id="xpi_wujc1" src="PPP-files/btwujc1_0.png" name="vbwujc1" width="185" height="25" border="0" alt="STUDENT AND RANKS                                                " /></a></td></tr><tr><td style="padding-bottom:0px" title ="HOSTELS BOARDERS                                             ">
                                                <a onmouseover='xpe("4ujc1o");xpshow("4ujc1",1,this);xpsmover(this);' onmouseout='xpsmout(this);'><img id="xpi_4ujc1" src="PPP-files/bt4ujc1_0.png" name="vb4ujc1" width="185" height="25" border="0" alt="HOSTELS BOARDERS                                             " /></a></td></tr><tr><td style="padding-bottom:0px" title ="ROOM WISE LISTS                                                     ">
                                                <a onmouseover='xpe("zujc1o");' onmouseout='xpe("zujc1n");'  onclick="tab(18)"><img id="xpi_zujc1" src="PPP-files/btzujc1_0.png" name="vbzujc1" width="185" height="25" border="0" alt="ROOM WISE LISTS                                                     " /></a></td></tr><tr><td style="padding-bottom:0px" title ="SHOW STUDENT DETAIL                               ">
                                                <a onmouseover='xpe("uujc1o");' onmouseout='xpe("uujc1n");' onclick="tab(19)"><img id="xpi_uujc1" src="PPP-files/btuujc1_0.png" name="vbuujc1" width="185" height="25" border="0" alt="SHOW STUDENT DETAIL                               " /></a></td></tr>
                                    </table>

                                </td>

                            </tr>
                        </table>
                    </td>
                    <td valign="top" align="left" bgcolor="#E8E8E8" WIDTH="595">
                        <center><br/>
                            <h1><font style="font-family: calibri; color: navy;">WELCOME ADMINISTRATOR</font></h1>
                            <table width="595" align="center" border="0" bgcolor="#E8E8E8">
                                <tr id ="t1" >
                                    <td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>BIOTECHNOLOGY</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=1 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td>
                                </tr>


                                <tr id ="t2" >
                                    <td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>CHEMICAL ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=2 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t3" >
                                    <td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>CIVIL&nbsp;ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=3 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td>
                                </tr>

                                <tr id ="t4" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>COMPUTER SCIENCE AND ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=4 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td>
                                </tr>

                                <tr id ="t5" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>ELECTRONICS AND COMMUNICATION ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=5 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t6" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>ELECTRICAL ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=6 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t7" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>INFORMATION TECHNOLOGY</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=7 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t8" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>MECHANICAL ENGINEERING</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=8 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t9" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>METALLURGICAL AND MATERIAL SCIENCES</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND BRANCHID=9 ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t10" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>GENERAL STUDENTS</h2></font></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2,STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='GEN' ORDER BY NO";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='GEN' ORDER BY NO";

                                                            rs = st.executeQuery(str);
                                                            %>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%

                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t11" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>OBC STUDENTS</h2></font></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2,STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='OBC' ORDER BY NO";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='OBC' ORDER BY NO";

                                                            rs = st.executeQuery(str);
                                                            %>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%

                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t12" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>SC STUDENTS</h2></font></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2,STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='SC' ORDER BY NO";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='SC' ORDER BY NO";

                                                            rs = st.executeQuery(str);
                                                            %>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%

                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t13" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>ST STUDENTS</h2></font></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2,STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='ST' ORDER BY NO";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2, STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID AND CATEGORY='ST' ORDER BY NO";

                                                            rs = st.executeQuery(str);
                                                            %>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%

                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t14" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>NIT BOYS HOSTEL</h2></font></b> </td></tr>

                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM ROOM_ALLOTMENTS R1, STUDENT_DETAILS R2 WHERE R1.SUID=R2.SUID AND HOSTEL_ID=1";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM ROOM_ALLOTMENTS R1, STUDENT_DETAILS R2 WHERE R1.SUID=R2.SUID AND HOSTEL_ID=1";

                                                            rs = st.executeQuery(str);
                                            %>
                                            <tr align="center"><th>ROOM ID</th><th>NAME</th><th>PHONE NO.</th></tr>
                                            <%
                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("room_id")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("phone_no")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t15" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>NIT GIRLS HOSTEL</h2></font></b> </td></tr>

                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT COUNT(*) FROM ROOM_ALLOTMENTS R1, STUDENT_DETAILS R2 WHERE R1.SUID=R2.SUID AND HOSTEL_ID=2";
                                                            rs = st.executeQuery(str);
                                                            rs.next();


                                            %>
                                            <tr><td  colspan="3" align="center" width="595" >Total No. of students : <b><i><%= rs.getString("count(*)")%></i></b> </td></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM ROOM_ALLOTMENTS R1, STUDENT_DETAILS R2 WHERE R1.SUID=R2.SUID AND HOSTEL_ID=2";

                                                            rs = st.executeQuery(str);
                                            %>
                                            <tr align="center"><th>ROOM ID</th><th>NAME</th><th>PHONE NO.</th></tr>
                                            <%
                                                            while (rs.next()) {

                                            %>

                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("room_id")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("phone_no")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>

                                <tr id ="t16" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>STUDENTS ENROLLED</h2></font></b> </td></tr>
                                            <tr align="center"><th>ROLL NO.</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT * FROM STUDENT_BRANCH_ROLL R1, STUDENT_DETAILS R2,STUDENT_RANKS R3 WHERE R1.SUID=R2.SUID AND R1.SUID=R3.SUID ORDER BY NO";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rollno")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>
                                <tr id ="t17" ><td width="595">

                                        <table align="center" border="1"width="595">
                                            <tr><td  colspan="3" align="center" width="595" ><b><font color="navy"> <h2>STUDENTS BY RANKS</h2></font></b> </td></tr>
                                            <tr align="center"><th>RANK</th><th>NAME</th><th>CATEGORY</th></tr>
                                            <%
                                                            st = null;
                                                            rs = null;
                                                            st = connection.createStatement();
                                                            str = "SELECT R1.SUID,SNAME, RANK, CATEGORY FROM STUDENT_RANKS R1, STUDENT_DETAILS R2 WHERE R1.SUID=R2.SUID ORDER BY RANK";

                                                            rs = st.executeQuery(str);

                                                            while (rs.next()) {

                                            %>
                                            <tr align="center">
                                                <td width="20%">   <%=rs.getString("rank")%></td>
                                                <td width="60%"align="left">&nbsp; &nbsp; <%=rs.getString("SName")%> </td>
                                                <td width="20%">   <%=rs.getString("category")%></td>
                                            </tr>
                                            <%
                                                            }
                                            %>
                                        </table>
                                    </td></tr>
                                <tr id ="t18" ><td width="595">
                                        <table align="center" width="595">
                                            <tr><td   align="center" width="595" ><b><font color="navy"> <h2>ROOM WISE LIST</h2></font></b> </td></tr>
                                            <tr><td>&nbsp;</td></tr>
                                            <tr><td align="center"><form method="POST" action="test.jsp">Enter the hostel id : <input type="text" name="hostelId">
                                            <br/><br/>Enter the room id : <input type="text" name="roomId">
                                            <br/><br/><input type="submit"  name="submit" value="Submit" >&nbsp;&nbsp;<input type="RESET" name="reset" value="Reset"></form></td></tr>
                                        </table>

                                    </td></tr>
                                <tr id ="t19" ><td width="595">
                                        <table align="center" width="595">
                                            <tr><td   align="center" width="595" ><b><font color="navy"> <h2>SHOW STUDENT DETAIL</h2></font></b> </td></tr>
                                            <tr><td>&nbsp;</td></tr>
                                            <tr><td align="center"><form method="POST" action="test1.jsp">Enter the Roll no. : <input type="text" name="roll">
                                            <br/><br/><input type="submit"  name="submit" value="Submit" >&nbsp;&nbsp;<input type="RESET" name="reset" value="Reset"></form></td></tr>
                                        </table>

                                    </td></tr>
                            </table>




                        </center></td></tr>
                        <tr ><td bgcolor="#E8E8E8" colspan="2" width="425" height="73" align="center" valign="middle"  style="line-height:22px"><font size="1"><a href="../about.jsp"> ABOUT US</a> | <a href="../developer.jsp">DEVELOPERS</a> | <a href="../contact.jsp">CONTACT US</a><br>

                            &#169 ONLINE ADMISSION SYSTEM, NATIONAL INSTITUTE OF TECHNOLOGY, DURGAPUR<BR> E-MAIL: webmaster@admin.nitdgp.ac.in</font></td>
                </tr>

            </table></center>
    </body>
</html>

<%

                // st.executeUpdate(str);
                connection.close();

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.out.println(" Could not find the database driver");
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("Could not connect to the database");
            }
%>